package cn;

import java.sql.*;
import java.util.Scanner;
import java.util.*;

public class MysqlTest3 {
	public static void main(String args[]) {
		Scanner in = new Scanner(System.in);
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
	    Connection con;
	    String sql, name, sex; 
	    int num, age;
		
	   try {
	      Class.forName("oracle.jdbc.odbc.JdbcOdbcDriver");
	   } catch(java.lang.ClassNotFoundException e) {
	       System.err.print("ClassNotFoundException: ");
	       System.err.println(e.getMessage());
	   }
	   try {	
		   // 对学号为1的学生的年龄进行修改，修改为22
           con = DriverManager.getConnection(url, "system", "system");
           sql = "update student set sage = 22 where sno = ?";       
           PreparedStatement stmt0=con.prepareStatement(sql);
            stmt0.setInt(1, 1);//设置第一个“？”参数值为sex变量的值
            ResultSet rs = stmt0.executeQuery(); 
            sql = "select * from student";
            PreparedStatement stmt1 = con.prepareStatement(sql);
            ResultSet re = stmt1.executeQuery();
            System.out.println("\n\n\n\n");
        	System.out.println("全体学生信息表");
        	System.out.println("-------------------------");
        	System.out.println("学号    姓名    性别  年龄");  
            System.out.println("-------------------------");
            //取出结果集rs中的所有记录
            while(re.next()){
        	     num = re.getInt(1); //取出当前行第1列数据
        	     name = re.getString(2); //取出当前行第2列数据
        	     sex = re.getString(3);
        	     age = re.getInt(4);        		 
        	     System.out.println(num+"    "+name+"   "+sex+"   "+age);
        	}       
            System.out.println("-------------------------");
       	
            // 根据学生的id对学生进行查找，并对学生的age进行修改
            System.out.println("请输入想查询年龄的学生的学号");
            int id = in.nextInt();
            sql = "select * from student";
            PreparedStatement stmt2 = con.prepareStatement(sql);
            ResultSet res = stmt2.executeQuery();
            boolean ok = false;
            while(res.next()) {
            	num = res.getInt(1);
            	name = res.getString(2);
            	sex = res.getString(3);
            	age = res.getInt(4);
            	if(num == id) {
            		System.out.println("学生的姓名:" + name + "    学生的性别:" + sex + "    学生的年龄:" + age);
            		ok = true;
            		System.out.println("请输入学生需要进行修改的年龄");
            		int temp = in.nextInt();
            		sql = "update student set sage = ? where sno = " + id;
            		PreparedStatement stem4 = con.prepareStatement(sql);
            		stem4.setInt(1, temp);
            		ResultSet res1 = stem4.executeQuery();
            		System.out.println("完成修改");
            		try {
            			stem4.close();
            			res1.close();
            		} catch(Exception e) {
            			e.printStackTrace();
            		}
            		break ;
            	}
            }
            if(!ok) System.out.println("没有找到学号为" + id + "的同学"); 
            
            //关闭连接
           	stmt0.close();
           	stmt1.close();
           	stmt2.close();
       	    con.close();
    	} catch(SQLException ex) {
           System.err.println("SQLException: " + ex.getMessage());
       }
    	in.close();
	}
}

